package ct.zxd.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import ct.zxd.bean.Cart;
import ct.zxd.bean.Dish;

/**
 * 
 * @author tzw
 *
 */
public class TCartDao {
	public List<Cart> selectCartsByUserId(String userId){
		Connection conn = null ;
		PreparedStatement pstm = null ;
		List<Cart> cartList = new ArrayList<Cart>();
		try {
			conn = DBconn.getconn();
		  String sql = "select userid,dishid,number,`check` from cart where userid = ?" ;
		  pstm = conn.prepareStatement(sql) ;
		  pstm.setString(1, userId);
			ResultSet rs = pstm.executeQuery() ;
			while(rs.next()) {
				Cart vo = new Cart() ;
				vo.setUserId(rs.getString(1));
				vo.setDishId(rs.getInt(2));
				vo.setNumber(rs.getInt(3));
				vo.setCheck(rs.getInt(4));
				vo.setDish(selectDishById(rs.getInt(2)));
				cartList.add(vo);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(!(conn == null)) {
				DBconn.closeconn(conn);
			}
		}
		return cartList ;
	}
	
	public Dish selectDishById(Integer dishId){
		Connection conn = null ;
		PreparedStatement pstm = null ;
		Dish vo = null ;
		try {
		  conn = DBconn.getconn();
		  String sql = "select id,`name`,imageURL,price,stockNumber,shop_id,category_name,minusStatuses,quantity,introduce,category_id,good_comment,comment_number "
				  		+ " from dish where id = ?";
		  pstm = conn.prepareStatement(sql) ;
		  pstm.setInt(1 , dishId);
		  ResultSet rs = pstm.executeQuery() ;
		     while(rs.next()) {
		    	 vo = new Dish();
				  vo.setDish_id(rs.getInt(1));
				  vo.setName(rs.getString(2));
				  vo.setImageURL(rs.getString(3)) ;
				  vo.setPrice(rs.getFloat(4));
				  vo.setStockNumber(rs.getInt(5));
				  vo.setShop_id(rs.getInt(6));
				  vo.setCategory_name(rs.getString(7));
				  vo.setMinusStatuses(rs.getString(8));
				  vo.setQuantity(rs.getInt(9));
				  vo.setIntroduce(rs.getString(10));
				  vo.setCategory_id(rs.getString(11));
				  vo.setGoodComment(rs.getFloat(12));
				  vo.setCommentNumber(rs.getInt(13));
		     }
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(!(conn == null)) {
				DBconn.closeconn(conn);
			}
		}
		return vo; 
	}
	
	@SuppressWarnings("null")
	public Cart selectDishByUserid(String userId , Integer dishId)  {
		Connection conn = null;
//		List<Cart> list=null;
		Cart vo = null ;
		try {
			conn =DBconn.getconn() ;
//			list = new ArrayList<Cart>() ;
//			String sql = "select userid,dishid,number,`check` from cart where dishid = ? GROUP BY userid  having userid = ?" ;
//			PreparedStatement pstm = conn.prepareStatement(sql) ;
//			pstm.setInt(1, dishid);
//			pstm.setString(2, userId);
//			ResultSet rs = pstm.executeQuery() ;
//			while(rs.next()) {
//				Cart vo = new Cart() ;
//				vo.setUserId(rs.getString(1));
//				vo.setDishId(rs.getInt(2));
//				vo.setNumber(rs.getInt(3));
//				vo.setCheck(rs.getInt(4));
//				list.add(vo);
			vo = new Cart() ;
			String sql = "select userid,dishid,number,`check` from cart where dishid = ? GROUP BY userid  having userid = ?" ;
			PreparedStatement pstm = conn.prepareStatement(sql) ;
			pstm.setInt(1, dishId);
			pstm.setString(2, userId);
			ResultSet rs = pstm.executeQuery() ;
			while(rs.next()) {
				vo.setUserId(rs.getString(1));
				vo.setDishId(rs.getInt(2));
				vo.setNumber(rs.getInt(3));
				vo.setCheck(rs.getInt(4));
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) {
				DBconn.closeconn(conn);
			}
		}
		return vo ;
	}
	
	public int insertCart(String userId , Integer dishId ,Integer number) {
		Connection conn = null ;
		PreparedStatement pstm = null ;
		int count = 0 ;
		try {
			conn = DBconn.getconn() ;
			String sql = "insert into cart(userid,dishid,number,`check`) values (?,?,?,?) " ;
			pstm = conn.prepareStatement(sql) ;
			pstm.setString(1 , userId);
			pstm.setInt(2, dishId);
			pstm.setInt(3, number);
			pstm.setInt(4, 0);
			count = pstm.executeUpdate() ;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) {
				DBconn.closeconn(conn);
			}
		}
		return count ;
	}
	public int updateAgainNumber(String userId , Integer dishId ,Integer number) {
		Connection conn = null ;
		PreparedStatement pstm = null ;
		int count = 0 ;
		try {
			conn = DBconn.getconn() ;
			String sql = "update cart set number = number + ? where userid = ? and dishid = ? " ;
			pstm = conn.prepareStatement(sql) ;
			pstm.setInt(1, number);
			pstm.setString(2 , userId);
			pstm.setInt(3, dishId);
			count = pstm.executeUpdate() ;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) {
				DBconn.closeconn(conn);
			}
		}
		return count ;
	}
	
	public int updateAddCartNumber(String userId , Integer dishId  ) {
		Connection conn = null ;
		PreparedStatement pstm = null ;
		int count = 0 ;
		try {
			conn = DBconn.getconn();
			String sql = "update cart set number = number + 1  where dishid = ? and userid = ?" ;
			pstm = conn.prepareStatement(sql) ;
			pstm.setInt(1, dishId);
			pstm.setString(2 , userId) ;
			count = pstm.executeUpdate() ;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(!(conn == null)) {
				DBconn.closeconn(conn);
			}
		}
		return count ;
	}
	
	public int updateDecCartNumber(String userId , Integer dishId  ) {
		Connection conn = null ;
		PreparedStatement pstm = null ;
		int count = 0 ;
		try {
			conn = DBconn.getconn();
			String sql = "update cart set number = number - 1  where dishid = ? and userid = ?" ;
			pstm = conn.prepareStatement(sql) ;
			pstm.setInt(1, dishId);
			pstm.setString(2 , userId) ;
			count = pstm.executeUpdate() ;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(!(conn == null)) {
				DBconn.closeconn(conn);
			}
		}
		return count ;
	}
	
	public int updateCartCheck(Integer dishId ,Integer check , String userId ) {
		Connection conn = null ;
		PreparedStatement pstm = null ;
		int count = 0 ;
		try {
			conn = DBconn.getconn();
			String sql = "update cart set `check` = ? where dishid = ? and userid = ?" ;
			pstm = conn.prepareStatement(sql) ;
			pstm.setInt(1, check);
			pstm.setInt(2, dishId);
			pstm.setString(3 , userId) ;
			count = pstm.executeUpdate() ;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(!(conn == null)) {
				DBconn.closeconn(conn);
			}
		}
		return count ;
	}
	
	public int deleteCart(Integer dishId, String userId) {
		Connection conn = null ;
		PreparedStatement pstm = null ;
		int count = 0 ;
		try {
			conn = DBconn.getconn();
			String sql = "delete from cart where dishid = ? and userid = ?" ;
			pstm = conn.prepareStatement(sql) ;
			pstm.setInt(1 , dishId) ;
			pstm.setString(2, userId) ;
			count = pstm.executeUpdate() ;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(!(conn == null)) {
				DBconn.closeconn(conn);
			}
		}
		return count ;
	}
}

//
//Connection conn = null ;
//PreparedStatement pstm = null ;
//try {
//	conn = DBconn.getconn();
//  String sql = "" ;
//  pstm = conn.prepareStatement(sql) ;
//  
//}catch(Exception e) {
//	e.printStackTrace();
//}finally {
//	if(!(conn == null)) {
//		DBconn.closeconn(conn);
//	}
//}
//}
